package ks.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ks.po.Course;
import ks.po.Teacher;

public class TeacherDAO
{
    public List<Teacher> getAll() throws Exception
    {
        return search("", "");
    }

    /**
     * 根据老师名 课程名查找 老师、 课程 、以及关联关系表，讲找到的老师封装成老师集合，并且为每一个老师封装其课程集合
     *
     * @param vname
     *            teachername老师名
     * @param sname
     *            coursename 课程名
     * @return 老师集合
     * @throws Exception
     */
    public List<Teacher> search(String vname, String sname) throws Exception
    {
        List<Teacher> teachers = new ArrayList<Teacher>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_ks", "root", "root");
            // 1.找符合条件的老师
//            String sql = "SELECT distinct t_teacher.* FROM db_ks.t_teacher_course INNER JOIN db_ks.t_teacher ON (t_teacher_course.teacherId = t_teacher.id) INNER JOIN db_ks.t_course ON (t_teacher_course.courseId = t_course.id) where t_teacher.name like ? and t_course.name like ?";
            String sql = "select distinct t_teacher.*\n" +
                    "from t_teacher, t_course, t_teacher_course\n" +
                    "where t_teacher.id = t_teacher_course.teacherId and\n" +
                    "      t_course.id = t_teacher_course.courseId and\n" +
                    "      t_teacher.name like ? and t_course.name like ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, "%" + vname + "%");
            ps.setString(2, "%" + sname + "%");
            rs = ps.executeQuery();
            while (rs.next())
            {
                Teacher v = new Teacher();// 这里查询的是t_teacher.* 所以使用Teacher封装
                v.setId(rs.getInt("id"));
                v.setName(rs.getString("name"));
                teachers.add(v);
            }

            // 2.循环上面找到的老师，根据老师id为每一个老师封装课程集合==>有teacherid 要找所有关联的course
            for (Teacher v : teachers)
            {
//                sql = "SELECT t_course.* FROM    db_ks.t_teacher_course    INNER JOIN db_ks.t_course         ON (t_teacher_course.courseId = t_course.id) where t_teacher_course.teacherId=?";
                sql = "select t_course.*\n" +
                        "from t_course,  t_teacher_course\n" +
                        "where t_teacher_course.courseId = t_course.id AND\n" +
                        "      t_teacher_course.teacherId=?";
                ps = con.prepareStatement(sql);
                ps.setInt(1, v.getId());
                rs = ps.executeQuery();
                while (rs.next())
                {
                    Course s = new Course();// 这里查找的是t_course.* 所以使用Course封装
                    s.setId(rs.getInt("id"));
                    s.setName(rs.getString("name"));
                    v.getcourses().add(s);
                }
            }

        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new Exception("找不到驱动:" + e.getMessage());
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw new Exception("SQL异常:" + e.getMessage());
        }
        finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (ps != null)
            {
                ps.close();
            }
            if (con != null)
            {
                con.close();
            }
        }
        return teachers;
    }

    /**
     * 1.将teacher.name 保存到 t_teacher 2.同时取得其 id
     * 3.将teacher.id和teacher.courses里面所有的sid存到t_teacher_course表中（vid,sid0） (vid,sid1)
     * (vid,sid2)
     *
     * @param teacher
     * @throws Exception
     */
    public void save(Teacher teacher) throws Exception
    {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_ks", "root", "root");
            //JDBC默认是事务自动提交  即所有的executeUpdate会立即更新到数据库，如果需要使用事务要 1  停止自动提交  2.在操作完成后手动提交  3 出现异常回滚
            //事务1  停止自动提交
            con.setAutoCommit(false);
            // 1.将teacher.name 保存到 t_teacher
            ps = con.prepareStatement("insert into t_teacher value(null,?)", PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, teacher.getName());
            ps.executeUpdate();
            // 2.同时取得其 id 首先需要对ps进行修改
            rs = ps.getGeneratedKeys();
            if (rs.next())
            {
                teacher.setId(rs.getInt(1));
            }
            // 3.将teacher.id和teacher.courses里面所有的sid存到t_teacher_course表中（vid,sid0）(vid,sid1) (vid,sid2)
            if (teacher.getcourses().size() > 0)
            {
                String sql = "insert into t_teacher_course values (?,?)";
                for (int i = 1; i < teacher.getcourses().size(); i++) // 如果teacher.courses的大小大余1才会执行的循环
                {
                    sql += ",(?,?)";
                }
                ps=con.prepareStatement(sql);
                int i=1;
                for(Course s:teacher.getcourses())
                {
                    ps.setInt(i++	, teacher.getId());
                    ps.setInt(i++   , s.getId());
                }
                ps.executeUpdate();
            }
//			事务2.在操作完成后手动提交
            con.commit();
        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new Exception("找不到驱动:" + e.getMessage());
        }
        catch (SQLException e)
        {
            //事务3 出现异常回滚
            if(con!=null)con.rollback();

            e.printStackTrace();
            throw new Exception("SQL异常:" + e.getMessage());
        }
        finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (ps != null)
            {
                ps.close();
            }
            if (con != null)
            {
                con.close();
            }
        }
    }

    /**
     * 根据老师Id删除老师表的对应一行记录
     *
     * @param teacherId
     *            teacherId 老师Id
     * @return void
     * @throws Exception
     */
    public void delete(int teacherId) throws Exception
    {
        Connection con = null;
        PreparedStatement ps = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_ks","root","root");//  协议://域名(ip):端口/资源（数据库名）
            ps = con.prepareStatement("delete from t_teacher where id=?");
            ps.setInt(1, teacherId);
            ps.executeUpdate();
        }
        catch(ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new Exception("找不到驱动:"+e.getMessage());//异常不能在底层丢失了
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw new Exception("数据库操作错误:"+e.getMessage());
        }
        finally
        {
            if(ps!=null)
            {
                ps.close();
            }
            if(con!=null)
            {
                con.close();
            }
        }
    }


}